import flask
from dotenv import load_dotenv
from flask import Flask, jsonify, request, Response

from backend.cache import MemoryCache
from backend.danwen.dquestion_milvus import DQuestionMilvus

# 加载环境变量
load_dotenv()

from functools import wraps


# 创建Flask应用
app = Flask(__name__, static_url_path='', static_folder='static_copy')

# 系统设置
cache = MemoryCache()  # 初始化内存缓存

# from vanna.local import LocalContext_OpenAI
# vn = LocalContext_OpenAI()

# 初始化DQuestionMilvus模型
vn = DQuestionMilvus()
vn.connect_to_sqlite(url="https://vanna.ai/Chinook.sqlite")

# sql = vn.generate_sql()
# df = vn.run_sql(sql=sql)

# NO NEED TO CHANGE ANYTHING BELOW THIS LINE
def requires_cache(fields):
    """创建装饰器，用于检查请求是否包含必需的缓存字段"""
    def decorator(f):
        @wraps(f)
        def decorated(*args, **kwargs):
            id = request.args.get('id')

            if id is None:
                return jsonify({"type": "error", "error": "No id provided"})
            
            for field in fields:
                if cache.get(id=id, field=field) is None:
                    return jsonify({"type": "error", "error": f"No {field} found"})
            
            field_values = {field: cache.get(id=id, field=field) for field in fields}
            
            # 将ID添加到参数中
            field_values['id'] = id

            return f(*args, **field_values, **kwargs)
        return decorated
    return decorator

@app.route('/api/v0/generate_questions', methods=['GET'])
def generate_questions():
    """生成可以用来提问的问题列表"""
    return jsonify({
        "type": "question_list", 
        "questions": vn.generate_questions(),
        "header": "Here are some questions you can ask:"
        })

@app.route('/api/v0/generate_sql', methods=['GET'])
def generate_sql():
    """根据自然语言问题生成SQL查询"""
    question = flask.request.args.get('question')

    if question is None:
        return jsonify({"type": "error", "error": "No question provided"})

    # 为问题生成唯一ID
    id = cache.generate_id(question=question)
    # 生成对应的SQL
    sql = vn.generate_sql(question=question, allow_llm_to_see_data=True)

    # 存入缓存
    cache.set(id=id, field='question', value=question)
    cache.set(id=id, field='sql', value=sql)

    return jsonify(
        {
            "type": "sql", 
            "id": id,
            "text": sql,
        })

@app.route('/api/v0/run_sql', methods=['GET'])
@requires_cache(['sql'])
def run_sql(id: str, sql: str):
    """执行缓存中的SQL查询并返回结果"""
    try:
        # 执行SQL查询
        df = vn.run_sql(sql=sql)

        # 将结果存入缓存
        cache.set(id=id, field='df', value=df)

        return jsonify(
            {
                "type": "df", 
                "id": id,
                "df": df.head(10).to_json(orient='records'),  # 返回前10行
            })

    except Exception as e:
        return jsonify({"type": "error", "error": str(e)})

@app.route('/api/v0/download_csv', methods=['GET'])
@requires_cache(['df'])
def download_csv(id: str, df):
    """将查询结果下载为CSV文件"""
    csv = df.to_csv()

    return Response(
        csv,
        mimetype="text/csv",
        headers={"Content-disposition":
                 f"attachment; filename={id}.csv"})

@app.route('/api/v0/generate_plotly_figure', methods=['GET'])
@requires_cache(['df', 'question', 'sql'])
def generate_plotly_figure(id: str, df, question, sql):
    """根据查询结果生成Plotly可视化图表"""
    try:
        # 获取数据框的元数据
        code = vn.generate_plotly_code(question=question, sql=sql, df_metadata=f"Running df.dtypes gives:\n {df.dtypes}")
        # 执行代码获取图表
        fig = vn.get_plotly_figure(plotly_code=code, df=df, dark_mode=False)
        fig_json = fig.to_json()

        # 保存图表JSON到缓存
        cache.set(id=id, field='fig_json', value=fig_json)

        return jsonify(
            {
                "type": "plotly_figure", 
                "id": id,
                "fig": fig_json,
            })
    except Exception as e:
        # 打印堆栈跟踪便于调试
        import traceback
        traceback.print_exc()

        return jsonify({"type": "error", "error": str(e)})

@app.route('/api/v0/get_training_data', methods=['GET'])
def get_training_data():
    """获取当前系统中的训练数据"""
    df = vn.get_training_data()

    return jsonify(
    {
        "type": "df", 
        "id": "training_data",
        "df": df.head(25).to_json(orient='records'),  # 返回前25行
    })

@app.route('/api/v0/remove_training_data', methods=['POST'])
def remove_training_data():
    """删除指定ID的训练数据"""
    # 从JSON请求体中获取ID
    id = flask.request.json.get('id')

    if id is None:
        return jsonify({"type": "error", "error": "No id provided"})

    if vn.remove_training_data(id=id):
        return jsonify({"success": True})
    else:
        return jsonify({"type": "error", "error": "Couldn't remove training data"})

@app.route('/api/v0/train', methods=['POST'])
def add_training_data():
    """添加新的训练数据到系统中"""
    question = flask.request.json.get('question')
    sql = flask.request.json.get('sql')
    ddl = flask.request.json.get('ddl')
    documentation = flask.request.json.get('documentation')

    try:
        id = vn.train(question=question, sql=sql, ddl=ddl, documentation=documentation)

        return jsonify({"id": id})
    except Exception as e:
        print("TRAINING ERROR", e)
        return jsonify({"type": "error", "error": str(e)})

@app.route('/api/v0/generate_followup_questions', methods=['GET'])
@requires_cache(['df', 'question', 'sql'])
def generate_followup_questions(id: str, df, question, sql):
    """根据当前问题和结果生成后续可能的问题"""
    # 生成后续问题
    followup_questions = vn.generate_followup_questions(question=question, sql=sql, df=df)

    # 将后续问题存入缓存
    cache.set(id=id, field='followup_questions', value=followup_questions)

    return jsonify(
        {
            "type": "question_list", 
            "id": id,
            "questions": followup_questions,
            "header": "Here are some followup questions you can ask:"
        })

@app.route('/api/v0/load_question', methods=['GET'])
@requires_cache(['question', 'sql', 'df', 'fig_json', 'followup_questions'])
def load_question(id: str, question, sql, df, fig_json, followup_questions):
    """从缓存中加载完整的问题和结果数据"""
    try:
        return jsonify(
            {
                "type": "question_cache", 
                "id": id,
                "question": question,
                "sql": sql,
                "df": df.head(10).to_json(orient='records'),
                "fig": fig_json,
                "followup_questions": followup_questions,
            })

    except Exception as e:
        return jsonify({"type": "error", "error": str(e)})

@app.route('/api/v0/get_question_history', methods=['GET'])
def get_question_history():
    """获取用户之前提问的历史记录"""
    return jsonify({"type": "question_history", "questions": cache.get_all(field_list=['question']) })

@app.route('/')
def root():
    """根路径，返回静态首页"""
    return app.send_static_file('index.html')

# 主程序入口
if __name__ == '__main__':
    print("服务启动: http://127.0.0.1:5000")
    # 启动Flask服务，开启调试模式
    app.run(debug=True)
